package cn.hit.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.List;

import cn.hit.been.HitCodeSortBeen;
import cn.hit.been.HitCodeValueBeen;
import cn.hit.constant.HitConstants;
import cn.hit.utils.HitUtils;

public class DBAccessSington {
    
    private Connection connection = null;
    private PreparedStatement stmt = null;
    private static DBAccessSington instance = new DBAccessSington();
    static {
        try {
            //加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private DBAccessSington() {
    }

    /*
     * 设置JDBC连接
     */
    public boolean setConnection(String url, String userName, String userPWD) {
        try {
            DriverManager.setLoginTimeout(HitConstants.DB_ACCESS_TIMEOUT);
            //创建连接
            connection = DriverManager.getConnection(url, userName, userPWD);
            connection.setAutoCommit(false);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 关闭JDBC连接
     */
    public void closeConnection() {
        try {
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量执行Sql语句
     * 
     * @param sql
     * @return 1：执行成功；-1：执行失败
     */
    public int excuteSql(String sql, StringBuilder exportLog) {
        if(connection != null){
            sql = sql.replaceAll("\ngo", "\nGO");
            String[] sqls = sql.split("\nGO");
            int[] resault = null;
            Savepoint point = null;
            try {
                point = connection.setSavepoint();
                for (String s : sqls) {
                    if (s.trim().length() > 1) {
                        stmt = connection.prepareStatement(s);
                        stmt.addBatch();
                        resault = stmt.executeBatch();
                    }
                }
                if (resault != null) {
                    connection.commit();
                    exportLog.append("**********执行完成\n");
                    return resault.length;
                } else {
                    exportLog.append("**********非法脚本\n");
                    return -1;
                }
            } catch (SQLException e) {
                e.printStackTrace();
                try {
                    connection.rollback(point);
                } catch (SQLException e1) {
                    e1.printStackTrace();
                    exportLog.append("**********非法脚本\n\t" + e.toString() + "\n\n");
                    return -1;
                }
                exportLog.append("**********非法脚本\n\t" + e.toString() + "\n\n");
                return -1;
            }
        } else {
            return -1;
        }
    }

    /**
     * 程序整个生命周期只能有一个实例
     * @return 返回连接数据库实例
     */
    public static DBAccessSington getInstance() {
        return instance;
    }

    /**
     * 设置码类相关联的信息
     * @param hitCodeSortBeen
     * @return 码值Id
     * @throws Exception
     */
    public void setCodeSortBeenRelationInfo(HitCodeSortBeen hitCodeSortBeen) throws Exception{
        String strTableName = HitConstants.DB_CODE_SORT_TABLE_NAME;
        String strIdFieldName = HitConstants.DB_CODE_SORT_TABLE_ID_FIELD_NAME;
        String strNoFieldName = HitConstants.DB_CODE_SORT_TABLE_NO_FIELD_NAME;
        String strNoValue = hitCodeSortBeen.getStrCodeSortNo();
        String codeSortId = queryIdByNo(strTableName,strIdFieldName,strNoFieldName,strNoValue);
        // 如果查询出来id是null，说明数据库里面不存在该码值，以后会进行新建操作，否则进行更新操作
        if(codeSortId == null){
            hitCodeSortBeen.setExist(false);
            hitCodeSortBeen.setStrCodeSortId("'" + HitUtils.getNewId() + "'");
            strTableName = HitConstants.DB_APP_INFO_TABLE_NAME;
            strIdFieldName = HitConstants.DB_APP_INFO_TABLE_ID_FIELD_NAME;
            strNoFieldName = HitConstants.DB_APP_INFO_TABLE_NO_FIELD_NAME;
            //strAppId现在存的是strAppNo
            strNoValue = hitCodeSortBeen.getStrAppId();
            String strAppId = queryIdByNo(strTableName,strIdFieldName,strNoFieldName,strNoValue);
            hitCodeSortBeen.setStrAppId("'" + strAppId + "'");
            strTableName = HitConstants.DB_USER_INFO_TABLE_NAME;
            strIdFieldName = HitConstants.DB_USER_INFO_TABLE_ID_FIELD_NAME;
            strNoFieldName = HitConstants.DB_USER_INFO_TABLE_NO_FIELD_NAME;
            //strCreateId现在存的是strCreateNo
            strNoValue = hitCodeSortBeen.getStrCreateId();
            String strCreateId = queryIdByNo(strTableName,strIdFieldName,strNoFieldName,strNoValue);
            hitCodeSortBeen.setStrCreateId("'" + strCreateId + "'");
        } else {
            hitCodeSortBeen.setExist(true);
            hitCodeSortBeen.setStrCodeSortId("'" + codeSortId + "'");
        }
        //把double类型的日期转换为date
        hitCodeSortBeen.setDatModifyTime(HitUtils.dateFomartForStringDouble(hitCodeSortBeen.getDatModifyTime()));
        //把码类Been的Id设置到其码值Been里面
        List<HitCodeValueBeen> codeValueBeen = hitCodeSortBeen.getCodeValueBeens();
        int numShowOrder = 1;
        for (HitCodeValueBeen hitCodeValueBeen : codeValueBeen) {
            hitCodeValueBeen.setStrCodeSortId(hitCodeSortBeen.getStrCodeSortId());
            hitCodeValueBeen.setStrCreateId(hitCodeSortBeen.getStrCreateId());
            hitCodeValueBeen.setNumShowOrder(String.valueOf(numShowOrder));
            //把double类型的日期转换为date
            hitCodeValueBeen.setDatModifyTime(HitUtils.dateFomartForStringDouble(hitCodeValueBeen.getDatModifyTime()));
            numShowOrder ++;
        }
    }
    
    /**
     * 设置码类相关联的信息
     * @param hitCodeSortBeen
     * @return 码值Id
     * @throws Exception
     */
    public void setCodeValueBeenRelationInfo(HitCodeValueBeen hitCodeValueBeen) throws Exception{
        hitCodeValueBeen.setStrCodeValueId("'" + HitUtils.getNewId() + "'");
        String strTableName = HitConstants.DB_USER_INFO_TABLE_NAME;
        String strIdFieldName = HitConstants.DB_USER_INFO_TABLE_ID_FIELD_NAME;
        String strNoFieldName = HitConstants.DB_USER_INFO_TABLE_NO_FIELD_NAME;
        //strCreateId现在存的是strCreateNo
        String strNoValue = hitCodeValueBeen.getStrCreateId();
        String strCreateId = queryIdByNo(strTableName,strIdFieldName,strNoFieldName,strNoValue);
        hitCodeValueBeen.setStrCreateId("'" + strCreateId + "'");
    }

    /**
     * 根据编号(NO)查询新对应的ID
     * @param strTableName 表名
     * @param strIdFieldName id字段名
     * @param strNoFieldName NO字段名
     * @param strNoValue NO条件值
     * @return 查询后的ID
     * @throws SQLException 
     */
    private String queryIdByNo(String strTableName,String strIdFieldName,String strNoFieldName,String strNoValue) throws SQLException{
        String sql = "select "+strIdFieldName+" from "+strTableName+" " +
        "where strDelFlg = '0' and "+strNoFieldName+" = "+strNoValue;
        stmt = connection.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        String id = null;
        while(rs.next()){
            id = rs.getString(strIdFieldName);
        }
        return id;
    }
}